using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;

namespace ASPNETVN.PORTAL.Modules.ACTP.Components
{
	public partial class ProjectEmployee
	{
		#region Properties.
		
		public int TeamID { set; get; }
		public int ProjectID { set; get; }
		public int EmployeeID { set; get; }
		public bool IsProjectManager { set; get; }
		public Guid ModuleID { set; get; }
		
		#endregion
		
		//---------------------------------------------------------------------------------------------
		
		#region Methods
		protected static IList<ProjectEmployee> ConvertToCollection(IDataReader reader)
		{
			IList<ProjectEmployee> collection = new List<ProjectEmployee>();
			while (reader.Read())
			{
				ProjectEmployee entity = new ProjectEmployee();
				if (!reader.IsDBNull(reader.GetOrdinal("TeamID"))) entity.TeamID = reader.GetInt32(reader.GetOrdinal("TeamID"));
				if (!reader.IsDBNull(reader.GetOrdinal("ProjectID"))) entity.ProjectID = reader.GetInt32(reader.GetOrdinal("ProjectID"));
				if (!reader.IsDBNull(reader.GetOrdinal("EmployeeID"))) entity.EmployeeID = reader.GetInt32(reader.GetOrdinal("EmployeeID"));
				if (!reader.IsDBNull(reader.GetOrdinal("IsProjectManager"))) entity.IsProjectManager = reader.GetBoolean(reader.GetOrdinal("IsProjectManager"));
				if (!reader.IsDBNull(reader.GetOrdinal("ModuleID"))) entity.ModuleID = reader.GetGuid(reader.GetOrdinal("ModuleID"));
				collection.Add(entity);
			}
			reader.Close();
			return collection;
		}
		
		#endregion
		
		//---------------------------------------------------------------------------------------------
		
		#region Select methods.
		
		public static ProjectEmployee Load(int teamID, int projectID, int employeeID)
		{
			const string spName = "[dbo].[p_ACTP_ProjectEmployee_Load]";
            SqlDatabase db = (SqlDatabase) DatabaseFactory.CreateDatabase();
			SqlCommand dbCommand = (SqlCommand) db.GetStoredProcCommand(spName);
			
			db.AddInParameter(dbCommand, "@TeamID", SqlDbType.Int, teamID);
			db.AddInParameter(dbCommand, "@ProjectID", SqlDbType.Int, projectID);
			db.AddInParameter(dbCommand, "@EmployeeID", SqlDbType.Int, employeeID);
            IDataReader reader = db.ExecuteReader(dbCommand);
			IList<ProjectEmployee> collection = ConvertToCollection(reader);	
			if (collection.Count > 0)
			{
				return collection[0];
			}
			return null;
		}		
		
		//---------------------------------------------------------------------------------------------
		public static IList<ProjectEmployee> SelectCollectionAll(Guid moduleID)
		{
			IDataReader reader = SelectReaderAll(moduleID);
			return ConvertToCollection(reader);			
		}		
		
		//---------------------------------------------------------------------------------------------
		
		public static IList<ProjectEmployee> SelectCollectionDynamic(string whereCondition, string orderByExpression)
		{
			IDataReader reader = SelectReaderDynamic(whereCondition, orderByExpression);
			return ConvertToCollection(reader);		
		}
		
		//---------------------------------------------------------------------------------------------
		
		// Select by foreign key return collection		
		public static IList<ProjectEmployee> SelectCollectionBy_TeamID(int teamID)
		{
            IDataReader reader = SelectReaderBy_TeamID(teamID);
			return ConvertToCollection(reader);	
		}		
		//---------------------------------------------------------------------------------------------
		public static IList<ProjectEmployee> SelectCollectionBy_EmployeeID(int employeeID)
		{
            IDataReader reader = SelectReaderBy_EmployeeID(employeeID);
			return ConvertToCollection(reader);	
		}		
		//---------------------------------------------------------------------------------------------
		public static IList<ProjectEmployee> SelectCollectionBy_ProjectID(int projectID)
		{
            IDataReader reader = SelectReaderBy_ProjectID(projectID);
			return ConvertToCollection(reader);	
		}		
		//---------------------------------------------------------------------------------------------
		
		public static DataSet SelectBy_TeamID(int teamID)
		{
			const string spName = "[dbo].[p_ACTP_ProjectEmployee_SelectBy_TeamID]";
            SqlDatabase db = (SqlDatabase) DatabaseFactory.CreateDatabase();
            SqlCommand dbCommand = (SqlCommand) db.GetStoredProcCommand(spName);
			
			db.AddInParameter(dbCommand, "@TeamID", SqlDbType.Int, teamID);
						
            return db.ExecuteDataSet(dbCommand);
		}
		//---------------------------------------------------------------------------------------------
		public static DataSet SelectBy_EmployeeID(int employeeID)
		{
			const string spName = "[dbo].[p_ACTP_ProjectEmployee_SelectBy_EmployeeID]";
            SqlDatabase db = (SqlDatabase) DatabaseFactory.CreateDatabase();
            SqlCommand dbCommand = (SqlCommand) db.GetStoredProcCommand(spName);
			
			db.AddInParameter(dbCommand, "@EmployeeID", SqlDbType.Int, employeeID);
						
            return db.ExecuteDataSet(dbCommand);
		}
		//---------------------------------------------------------------------------------------------
		public static DataSet SelectBy_ProjectID(int projectID)
		{
			const string spName = "[dbo].[p_ACTP_ProjectEmployee_SelectBy_ProjectID]";
            SqlDatabase db = (SqlDatabase) DatabaseFactory.CreateDatabase();
            SqlCommand dbCommand = (SqlCommand) db.GetStoredProcCommand(spName);
			
			db.AddInParameter(dbCommand, "@ProjectID", SqlDbType.Int, projectID);
						
            return db.ExecuteDataSet(dbCommand);
		}
		//---------------------------------------------------------------------------------------------

		public static DataSet SelectAll(Guid moduleID)
        {
            const string spName = "[dbo].[p_ACTP_ProjectEmployee_SelectAll]";
            SqlDatabase db = (SqlDatabase) DatabaseFactory.CreateDatabase();
            SqlCommand dbCommand = (SqlCommand) db.GetStoredProcCommand(spName);

			db.AddInParameter(dbCommand, "@ModuleID", SqlDbType.UniqueIdentifier, moduleID);
            return db.ExecuteDataSet(dbCommand);
        }
		
		//---------------------------------------------------------------------------------------------
		
		public static DataSet SelectDynamic(string whereCondition, string orderByExpression)
		{
            const string spName = "[dbo].[p_ACTP_ProjectEmployee_SelectDynamic]";
            SqlDatabase db = (SqlDatabase) DatabaseFactory.CreateDatabase();
            SqlCommand dbCommand = (SqlCommand) db.GetStoredProcCommand(spName);

			db.AddInParameter(dbCommand, "@WhereCondition", SqlDbType.NVarChar, whereCondition);
            db.AddInParameter(dbCommand, "@OrderByExpression", SqlDbType.NVarChar, orderByExpression);
            
            return db.ExecuteDataSet(dbCommand);        				
		}
		
		//---------------------------------------------------------------------------------------------
				
		public static IDataReader SelectReaderAll(Guid moduleID)
        {
            const string spName = "[dbo].[p_ACTP_ProjectEmployee_SelectAll]";
            SqlDatabase db = (SqlDatabase) DatabaseFactory.CreateDatabase();
            SqlCommand dbCommand = (SqlCommand) db.GetStoredProcCommand(spName);
			db.AddInParameter(dbCommand, "@ModuleID", SqlDbType.UniqueIdentifier, moduleID);
            return db.ExecuteReader(dbCommand);
        }
		
		//---------------------------------------------------------------------------------------------
		
		public static IDataReader SelectReaderDynamic(string whereCondition, string orderByExpression)
		{
            const string spName = "[dbo].[p_ACTP_ProjectEmployee_SelectDynamic]";
            SqlDatabase db = (SqlDatabase) DatabaseFactory.CreateDatabase();
            SqlCommand dbCommand = (SqlCommand) db.GetStoredProcCommand(spName);

			db.AddInParameter(dbCommand, "@WhereCondition", SqlDbType.NVarChar, whereCondition);
            db.AddInParameter(dbCommand, "@OrderByExpression", SqlDbType.NVarChar, orderByExpression);
            
            return db.ExecuteReader(dbCommand);        				
		}
		
		//---------------------------------------------------------------------------------------------
		
		// Select by foreign key return collection		
		public static IDataReader SelectReaderBy_TeamID(int teamID)
		{
			const string spName = "p_ACTP_ProjectEmployee_SelectBy_TeamID";
            SqlDatabase db = (SqlDatabase) DatabaseFactory.CreateDatabase();
			SqlCommand dbCommand = (SqlCommand) db.GetStoredProcCommand(spName);
			
			db.AddInParameter(dbCommand, "@TeamID", SqlDbType.Int, teamID);
			
            return db.ExecuteReader(dbCommand);
		}		
		//---------------------------------------------------------------------------------------------
		public static IDataReader SelectReaderBy_EmployeeID(int employeeID)
		{
			const string spName = "p_ACTP_ProjectEmployee_SelectBy_EmployeeID";
            SqlDatabase db = (SqlDatabase) DatabaseFactory.CreateDatabase();
			SqlCommand dbCommand = (SqlCommand) db.GetStoredProcCommand(spName);
			
			db.AddInParameter(dbCommand, "@EmployeeID", SqlDbType.Int, employeeID);
			
            return db.ExecuteReader(dbCommand);
		}		
		//---------------------------------------------------------------------------------------------
		public static IDataReader SelectReaderBy_ProjectID(int projectID)
		{
			const string spName = "p_ACTP_ProjectEmployee_SelectBy_ProjectID";
            SqlDatabase db = (SqlDatabase) DatabaseFactory.CreateDatabase();
			SqlCommand dbCommand = (SqlCommand) db.GetStoredProcCommand(spName);
			
			db.AddInParameter(dbCommand, "@ProjectID", SqlDbType.Int, projectID);
			
            return db.ExecuteReader(dbCommand);
		}		
		//---------------------------------------------------------------------------------------------
		
		#endregion
		
		//---------------------------------------------------------------------------------------------
		
		#region Insert methods.
		
		public static int InsertProjectEmployee(int teamID, int projectID, int employeeID, bool isProjectManager, Guid moduleID)
		{
			ProjectEmployee entity = new ProjectEmployee();	
			entity.TeamID = teamID;
			entity.ProjectID = projectID;
			entity.EmployeeID = employeeID;
			entity.IsProjectManager = isProjectManager;
			entity.ModuleID = moduleID;
			return entity.Insert();
		}
		
		public int Insert()
		{
			return this.Insert(null);
		}		
		
		//---------------------------------------------------------------------------------------------
		
		public int Insert(SqlTransaction transaction)
		{			
			const string spName = "[dbo].[p_ACTP_ProjectEmployee_Insert]";		
            SqlDatabase db = (SqlDatabase) DatabaseFactory.CreateDatabase();
            SqlCommand dbCommand = (SqlCommand) db.GetStoredProcCommand(spName);

			db.AddInParameter(dbCommand, "@TeamID", SqlDbType.Int, TeamID);
			db.AddInParameter(dbCommand, "@ProjectID", SqlDbType.Int, ProjectID);
			db.AddInParameter(dbCommand, "@EmployeeID", SqlDbType.Int, EmployeeID);
			db.AddInParameter(dbCommand, "@IsProjectManager", SqlDbType.Bit, IsProjectManager);
			db.AddInParameter(dbCommand, "@ModuleID", SqlDbType.UniqueIdentifier, ModuleID);
			
			if (transaction != null)
			{
				return db.ExecuteNonQuery(dbCommand, transaction);
			}
            else
			{
				return db.ExecuteNonQuery(dbCommand);
			}			
		}
				
		//---------------------------------------------------------------------------------------------
		public static bool InsertCollection(IList<ProjectEmployee> collection)
        {
            bool ret;
			SqlDatabase db = (SqlDatabase) DatabaseFactory.CreateDatabase();
            using (SqlConnection connection = (SqlConnection)db.CreateConnection())
            {
                connection.Open();
                using(SqlTransaction transaction = connection.BeginTransaction())
				{
					try
					{
						bool ret01 = true;
						foreach (ProjectEmployee item in collection)
						{
							if (item.Insert(transaction) <= 0)
							{							
								ret01 = false;
								break;
							}
						}
						if (ret01)
						{
							transaction.Commit();
							ret = true;
						}
						else
						{
							transaction.Rollback();
							ret = false;                    	
						}
					}
					catch (Exception ex)
					{
						transaction.Rollback();
						throw new Exception("Error at InsertCollection method: " + ex.Message);
					}
					finally 
					{
						connection.Close();
					}
				}
            }
            return ret;		
		}
		
		#endregion
		
		//---------------------------------------------------------------------------------------------
		
		#region Insert / Update methods.
		
		public static int InsertUpdateProjectEmployee(int teamID, int projectID, int employeeID, bool isProjectManager, Guid moduleID)
		{
			ProjectEmployee entity = new ProjectEmployee();			
			entity.TeamID = teamID;
			entity.ProjectID = projectID;
			entity.EmployeeID = employeeID;
			entity.IsProjectManager = isProjectManager;
			entity.ModuleID = moduleID;
			return entity.InsertUpdate();
		}
		
		//---------------------------------------------------------------------------------------------
		
		public int InsertUpdate()
		{
			return this.InsertUpdate(null);
		}
		
		//---------------------------------------------------------------------------------------------

		public int InsertUpdate(SqlTransaction transaction)
		{			
			const string spName = "p_ACTP_ProjectEmployee_InsertUpdate";		
            SqlDatabase db = (SqlDatabase) DatabaseFactory.CreateDatabase();
			SqlCommand dbCommand = (SqlCommand) db.GetStoredProcCommand(spName);

			db.AddInParameter(dbCommand, "@TeamID", SqlDbType.Int, TeamID);
			db.AddInParameter(dbCommand, "@ProjectID", SqlDbType.Int, ProjectID);
			db.AddInParameter(dbCommand, "@EmployeeID", SqlDbType.Int, EmployeeID);
			db.AddInParameter(dbCommand, "@IsProjectManager", SqlDbType.Bit, IsProjectManager);
			db.AddInParameter(dbCommand, "@ModuleID", SqlDbType.UniqueIdentifier, ModuleID);
			
			if (transaction != null)
                return db.ExecuteNonQuery(dbCommand, transaction);
            else
                return db.ExecuteNonQuery(dbCommand);			
		}
		
		//---------------------------------------------------------------------------------------------
		public static bool InsertUpdateCollection(IList<ProjectEmployee> collection)
        {
            bool ret;
			SqlDatabase db = (SqlDatabase) DatabaseFactory.CreateDatabase();
            using (SqlConnection connection = (SqlConnection)db.CreateConnection())
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
				{
					try
					{
						bool ret01 = true;
						foreach (ProjectEmployee item in collection)
						{
							if (item.InsertUpdate(transaction) <= 0)
							{
								ret01 = false;
								break;
							}
						}
						if (ret01)
						{
							transaction.Commit();
							ret = true;
						}
						else
						{
							transaction.Rollback();
							ret = false;                    	
						}
					}
					catch (Exception ex)
					{
						transaction.Rollback();
						throw new Exception("Error at InsertUpdateCollection method: " + ex.Message);
						
					}
					finally 
					{
						connection.Close();
					}
				}
            }
            return ret;		
		}	
		#endregion
		
		//---------------------------------------------------------------------------------------------
		
		#region Update methods.
		
		public static int UpdateProjectEmployee(int teamID, int projectID, int employeeID, bool isProjectManager, Guid moduleID)
		{
			ProjectEmployee entity = new ProjectEmployee();			
			entity.TeamID = teamID;
			entity.ProjectID = projectID;
			entity.EmployeeID = employeeID;
			entity.IsProjectManager = isProjectManager;
			entity.ModuleID = moduleID;
			return entity.Update();
		}
		
		//---------------------------------------------------------------------------------------------
		
		public int Update()
		{
			return this.Update(null);
		}
		
		//---------------------------------------------------------------------------------------------

		public int Update(SqlTransaction transaction)
		{
			const string spName = "[dbo].[p_ACTP_ProjectEmployee_Update]";		
			SqlDatabase db = (SqlDatabase) DatabaseFactory.CreateDatabase();
			SqlCommand dbCommand = (SqlCommand) db.GetStoredProcCommand(spName);

			db.AddInParameter(dbCommand, "@TeamID", SqlDbType.Int, TeamID);
			db.AddInParameter(dbCommand, "@ProjectID", SqlDbType.Int, ProjectID);
			db.AddInParameter(dbCommand, "@EmployeeID", SqlDbType.Int, EmployeeID);
			db.AddInParameter(dbCommand, "@IsProjectManager", SqlDbType.Bit, IsProjectManager);
			db.AddInParameter(dbCommand, "@ModuleID", SqlDbType.UniqueIdentifier, ModuleID);
			
			if (transaction != null)
                return db.ExecuteNonQuery(dbCommand, transaction);
            else
                return db.ExecuteNonQuery(dbCommand);
		}
				
		//---------------------------------------------------------------------------------------------
		public static bool UpdateCollection(IList<ProjectEmployee> collection)
        {
            bool ret;
			SqlDatabase db = (SqlDatabase) DatabaseFactory.CreateDatabase();
            using (SqlConnection connection = (SqlConnection)db.CreateConnection())
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
				{
					try
					{
						bool ret01 = true;
						foreach (ProjectEmployee item in collection)
						{
							if (item.Update(transaction) <= 0)
							{
								ret01 = false;
								break;
							}
						}
						if (ret01)
						{
							transaction.Commit();
							ret = true;
						}
						else
						{
							transaction.Rollback();
							ret = false;                    	
						}
					}
					catch (Exception ex)
					{
						transaction.Rollback();
						throw new Exception("Error at UpdateCollection method: " + ex.Message);
					}
					finally 
					{
						connection.Close();
					}
				}
            }
            return ret;		
		}
		
		#endregion
		
		//---------------------------------------------------------------------------------------------
		
		#region Delete methods.
		
		public static int DeleteProjectEmployee(int teamID, int projectID, int employeeID)
		{
			ProjectEmployee entity = new ProjectEmployee();
			entity.TeamID = teamID;
			entity.ProjectID = projectID;
			entity.EmployeeID = employeeID;
			
			return entity.Delete();
		}
		
		public int Delete()
		{
			return this.Delete(null);
		}
		
		//---------------------------------------------------------------------------------------------

		public int Delete(SqlTransaction transaction)
		{
			const string spName = "[dbo].[p_ACTP_ProjectEmployee_Delete]";		
			SqlDatabase db = (SqlDatabase) DatabaseFactory.CreateDatabase();
			SqlCommand dbCommand = (SqlCommand) db.GetStoredProcCommand(spName);
			
			db.AddInParameter(dbCommand, "@TeamID", SqlDbType.Int, TeamID);
			db.AddInParameter(dbCommand, "@ProjectID", SqlDbType.Int, ProjectID);
			db.AddInParameter(dbCommand, "@EmployeeID", SqlDbType.Int, EmployeeID);
			
			if (transaction != null)
                return db.ExecuteNonQuery(dbCommand, transaction);
            else
                return db.ExecuteNonQuery(dbCommand);
		}
		
		//---------------------------------------------------------------------------------------------
		
		public static int DeleteBy_TeamID(int teamID)
		{
			const string spName = "[dbo].[p_ACTP_ProjectEmployee_DeleteBy_TeamID]";
            SqlDatabase db = (SqlDatabase) DatabaseFactory.CreateDatabase();
            SqlCommand dbCommand = (SqlCommand) db.GetStoredProcCommand(spName);
			
			db.AddInParameter(dbCommand, "@TeamID", SqlDbType.Int, teamID);
						
            return db.ExecuteNonQuery(dbCommand);
		}
		
		//---------------------------------------------------------------------------------------------
			
		public static int DeleteBy_EmployeeID(int employeeID)
		{
			const string spName = "[dbo].[p_ACTP_ProjectEmployee_DeleteBy_EmployeeID]";
            SqlDatabase db = (SqlDatabase) DatabaseFactory.CreateDatabase();
            SqlCommand dbCommand = (SqlCommand) db.GetStoredProcCommand(spName);
			
			db.AddInParameter(dbCommand, "@EmployeeID", SqlDbType.Int, employeeID);
						
            return db.ExecuteNonQuery(dbCommand);
		}
		
		//---------------------------------------------------------------------------------------------
			
		public static int DeleteBy_ProjectID(int projectID)
		{
			const string spName = "[dbo].[p_ACTP_ProjectEmployee_DeleteBy_ProjectID]";
            SqlDatabase db = (SqlDatabase) DatabaseFactory.CreateDatabase();
            SqlCommand dbCommand = (SqlCommand) db.GetStoredProcCommand(spName);
			
			db.AddInParameter(dbCommand, "@ProjectID", SqlDbType.Int, projectID);
						
            return db.ExecuteNonQuery(dbCommand);
		}
		
		//---------------------------------------------------------------------------------------------
			
		
		public static int DeleteDynamic(string whereCondition)
		{
			const string spName = "[dbo].[p_ACTP_ProjectEmployee_DeleteDynamic]";
            SqlDatabase db = (SqlDatabase) DatabaseFactory.CreateDatabase();
            SqlCommand dbCommand = (SqlCommand) db.GetStoredProcCommand(spName);

			db.AddInParameter(dbCommand, "@WhereCondition", SqlDbType.NVarChar, whereCondition);
            
            return db.ExecuteNonQuery(dbCommand);   
		}
		//---------------------------------------------------------------------------------------------
		
		public static bool DeleteCollection(IList<ProjectEmployee> collection)
        {
            bool ret;
			SqlDatabase db = (SqlDatabase) DatabaseFactory.CreateDatabase();
            using (SqlConnection connection = (SqlConnection)db.CreateConnection())
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
				{
					try
					{
						bool ret01 = true;
						foreach (ProjectEmployee item in collection)
						{
							if (item.Delete(transaction) <= 0)
							{
								ret01 = false;
								break;
							}
						}
						if (ret01)
						{
							transaction.Commit();
							ret = true;
						}
						else
						{
							transaction.Rollback();
							ret = false;                    	
						}
					}
					catch (Exception ex)
					{
						transaction.Rollback();
						throw new Exception("Error at DeleteCollection method: " + ex.Message);
					}
					finally 
					{
						connection.Close();
					}
				}
            }
            return ret;		
		}
		#endregion
	}	
}